package com.finesys.backend.common.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Component;
/**
 * <p>Excel导出工具类</p>
 * @author lehoon
 * @date 2020-10-12
 */
@Component
public class ExcelUtil {
    /** 
     * 导出到excel - 写到临时文件输出 
     * @param params 
     * @return 
     * @throws IOException 
     */  
    public static String getExcelPath(Map<String, Object> params, String parentPath) throws Exception {  
    	HSSFWorkbook workBook = new HSSFWorkbook();
    	createSheet(params,workBook);  
        
        /* 
         * 输出新建的excel，保存到本地临时文件 
         */  
    	String uuid = UuidUtil.get32UUID();
    	String filePath = parentPath + uuid + ".xls";
        File tempFile = new File(filePath);  
        if (!tempFile.exists()) {  
            tempFile.getParentFile().mkdir();
            tempFile.createNewFile();
        }  
        OutputStream fileOut = new FileOutputStream(tempFile);          
        workBook.write(fileOut);  
        fileOut.flush();  
        fileOut.close();  
        return uuid; 
    }  
      
      
  
    /** 
     * 创建一个工作表，设置格式 
     * @param params 
     * @param workBook 
     */  
    private static void createSheet(Map<String, Object> params, Workbook workBook) {  
        // 创建一个工作薄对象  
        Sheet sheet = workBook.createSheet();  
          
        /* 
         * 创建样式对象 
         */  
        CellStyle titleCellStyle = workBook.createCellStyle();   
        // 指定单元格居中对齐，边框为细    
        titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);    
        titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);    
        titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);    
        titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);    
        titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);    
        titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);    
        // 设置填充色    
        titleCellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);    
        titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);    
        // 指定当单元格内容显示不下时自动换行    
        titleCellStyle.setWrapText(true);    
        // 设置单元格字体    
        Font titleFont = workBook.createFont();    
        titleFont.setFontHeightInPoints((short) 12);    
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    
        titleCellStyle.setFont(titleFont);    
          
        /* 
         *  创建单元格样式   
         */  
        CellStyle cellStyle = workBook.createCellStyle();  
        // 指定单元格居中对齐，边框为细    
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);    
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);    
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);    
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);    
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);    
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);    
        // 设置单元格字体    
        Font font = workBook.createFont();    
        titleFont.setFontHeightInPoints((short) 11);    
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);    
        cellStyle.setFont(font);  
          
        setExportData(sheet,titleCellStyle,cellStyle,params);  
          
    }  
      
    /** 
     * 设置导出数据 
     */
    private static void setExportData(Sheet sheet,CellStyle titleCellStyle,CellStyle cellStyle,Map<String, Object> params) {  
        /* 
         * 设置表头 
         */  
        Row headerRow = sheet.createRow(0);  
        Cell headerCell = null;  
        List<String> headers = (List<String>) params.get("titles");
        int len = headers.size();
        for (int i = 0; i <= len; i++) {    
            headerCell  = headerRow.createCell(i);    
            headerCell.setCellStyle(titleCellStyle);  
            if (i == 0) {
            	headerCell.setCellValue("序号");
            }
            if (i > 0) {
            	headerCell.setCellValue(headers.get(i - 1));    
            }
            sheet.setColumnWidth(i, (30 * 160));    
        }
          
        /* 
         * 设置正文 
         */  
       List<Map<String,Object>> datas = (List<Map<String, Object>>) params.get("dataList");  
        int i = 1;//正文从第二行开始  
        for (Map<String,Object> map : datas) {  
        	Row row = sheet.createRow(i);  
            for(int j=0;j <= len;j++){
				Cell cell = row.createCell(j);
				cell.setCellStyle(cellStyle);    
				if (j == 0) {
					cell.setCellValue(i);
				}
				if (j > 0) {
					String value = map.get("var"+j) != null ? map.get("var"+j).toString() : "";
					cell.setCellValue(value);  
				}
			}
            i++;
        }
    }
}
